/**
 * 项目名称:账务的数据层类
 *
 * @Author: 夏国倩
 * @Date: 2023/10/17 10:15
 * @Copyright
 */
package itSystemCase6.dao;

import itSystemCase6.domain.ZhangWuBean;
import itSystemCase6.tools.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class ZhangWuDao {
    public List<ZhangWuBean> selectAll() {

        List<ZhangWuBean> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from gjp_zhangwu";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("zwid");
                String flname = rs.getString("flname");
                double money = rs.getDouble("money");
                String zhanghu = rs.getString("zhanghu");
                String createtime = rs.getString("createtime");
                String description = rs.getString("description");

                //将结果集的每行数据存进对象list中
                ZhangWuBean z = new ZhangWuBean();
                z.setZwid(id);
                z.setFlname(flname);
                z.setMoney(money);
                z.setZhangHu(zhanghu);
                z.setCreatetime(createtime);
                z.setDescription(description);

                //将对象封装到List中
                list.add(z);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return null;
    }

    public void addZhangWu(ZhangWuBean zw) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "insert into gjp_zhangwu(flname,money,zhanghu,createtime,description) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, zw.getFlname());
            ps.setDouble(2, zw.getMoney());
            ps.setString(3, zw.getZhangHu());
            ps.setString(4, zw.getCreatetime());
            ps.setString(5, zw.getDescription());

            int count = ps.executeUpdate();
            System.out.println("受影响的条数：" + count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, ps, null);
        }
    }

    public List<ZhangWuBean> select(String startTime, String endTime) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<ZhangWuBean> list = new ArrayList<>();
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from gjp_zhangwu where createtime between ? and ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, startTime);
            ps.setString(2, endTime);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("zwid");
                String flname = rs.getString("flname");
                double money = rs.getDouble("money");
                String zhanghu = rs.getString("zhanghu");
                String createtime = rs.getString("createtime");
                String description = rs.getString("description");

                //将结果集的每行数据存进对象list中
                ZhangWuBean z = new ZhangWuBean(id, flname, money, zhanghu, createtime, description);
                list.add(z);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return null;
    }

    //调用update去更新数据库中的gjp_zhangwu
    public void editZhangWu(int id, int hh) {
        Scanner sc = new Scanner(System.in);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int count = 0;
        try {
            conn = DBUtil.getConnection();
            switch (hh) {
                case 1:
                    //更新类别
                    sql = "update gjp_zhangwu set flname = ? where zwid = " + id;
                    ps = conn.prepareStatement(sql);
                    System.out.print("请输入要修改的类别信息：");
                    ps.setString(1, sc.next());
                    count = ps.executeUpdate();
                    System.out.println("受影响的条数：" + count);
                    break;
                case 2:
                    //更新账户
                    sql = "update gjp_zhangwu set zhanghu = ? where zwid = " + id;
                    ps = conn.prepareStatement(sql);
                    System.out.print("请输入要修改的账户信息：");
                    ps.setString(1, sc.next());
                    count = ps.executeUpdate();
                    System.out.println("受影响的条数：" + count);
                    break;
                case 3:
                    //更新金额
                    sql = "update gjp_zhangwu set money = ? where zwid = " + id;
                    ps = conn.prepareStatement(sql);
                    System.out.print("请输入要修改的金额信息：");
                    ps.setString(1, sc.next());
                    count = ps.executeUpdate();
                    System.out.println("受影响的条数：" + count);
                    break;
                case 4:
                    //更新时间
                    sql = "update gjp_zhangwu set createtime = ? where zwid = " + id;
                    ps = conn.prepareStatement(sql);
                    System.out.print("请输入要修改的时间信息：");
                    ps.setString(1, sc.next());
                    count = ps.executeUpdate();
                    System.out.println("受影响的条数：" + count);
                    break;
                case 5:
                    //更新说明
                    sql = "update gjp_zhangwu set description = ? where zwid = " + id;
                    ps = conn.prepareStatement(sql);
                    System.out.print("请输入要修改的说明信息：");
                    ps.setString(1, sc.next());
                    count = ps.executeUpdate();
                    System.out.println("受影响的条数：" + count);
                    break;
                default:
                    //如果输入其他数字
                    System.out.println("输入错误！");
                    break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }
    public void deleteZhangWu(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from gjp_zhangwu where zwid=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, ps, null);
        }
    }
}
